package com.kunbo.cn.service.impl.core.poundlist;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import com.kunbo.cn.dao.core.poundlist.SalesPoundListDao;
import com.kunbo.cn.dao.system.BusinessCodeDao;
import com.kunbo.cn.entity.core.poundlist.PurchasePoundList;
import com.kunbo.cn.entity.core.poundlist.SalesPoundList;
import com.kunbo.cn.entity.core.vo.PurchasePoundListVo;
import com.kunbo.cn.entity.system.BusinessCode;
import com.kunbo.cn.enumeration.core.logistics.CarState;
import com.kunbo.cn.enumeration.core.logistics.DriverState;
import com.kunbo.cn.service.core.poundlist.SalesPoundListService;
import com.kunbo.cn.util.NumberCovertChinese;
import com.kunbo.cn.util.common.Array;
import com.kunbo.cn.util.date.CommonsUtil;
import com.kunbo.cn.util.date.SDate;
import com.kunbo.cn.util.json.FastJsonUtils;
import com.kunbo.cn.util.retu.CommonResult;
@Service
public class SalesPoundListServiceImpl implements SalesPoundListService {
    
	@Autowired
	private SalesPoundListDao dao;
	@Autowired
	private BusinessCodeDao businessCodeDao;
	@Autowired
    private JdbcTemplate jdbc;
	@Override
	public void save(String type,String jsonStr) {
		SalesPoundList t = FastJsonUtils.toBean(jsonStr, SalesPoundList.class);
		String str = "XSBD";
		Integer n = 0;
		String nStr = "";
		if(!"".equals(type) && "THTZD".equals(type)) {
			t.setLightCarTime(SDate.getSystemDateToString());//设置重车时间
		}
		BusinessCode b = businessCodeDao.findByTypeAndDate(str, SDate.getSystemDateYMDToString());
		if(null==b) {
			businessCodeDao.updateNumberAndDate(1, SDate.getSystemDateYMDToString());
			b = businessCodeDao.findByTypeAndDate(str, SDate.getSystemDateYMDToString());
		}
		n = b.getNumber();
		if(n>0 && n<10) {
			nStr ="000" +n;
		}else if(n>=10 && n<100) {
			nStr ="00" +n;
		}else if(n>=100 && n<1000) {
			nStr ="0" +n;
		}else {
			nStr ="" +n;
		}
		t.setXsPoundNumber(str+SDate.getSystemDateYMD2ToString()+nStr);
		t.setState("1");
		t.setCreateTime(SDate.getSystemDateToString());
		dao.save(t);

	}

	@Override
	public Map<String,Object> update(String type,String jsonStr) {
		Map<String,Object> rm = new HashMap<String,Object>();
		SalesPoundList t = FastJsonUtils.toBean(jsonStr, SalesPoundList.class);
		//以BigDecimal类型包装毛重
		BigDecimal brw = new BigDecimal(String.valueOf(t.getRoughWeight()));
		//以BigDecimal类型包装毛重
		BigDecimal bt = new BigDecimal(String.valueOf(t.getTare()));
		t.setNetWeight(brw.subtract(bt).doubleValue());
		System.out.println("类型为："+type);
		if(!"".equals(type) && "THTZD".equals(type)) {
			t.setLightCarTime(SDate.getSystemDateToString());//设空车时间
		}
		t.setLightCarTime(SDate.getSystemDateToString());//设空车时间
		t.setState("2");
		t.setLastModifiedTime(SDate.getSystemDateToString());
		dao.save(t);
		int carUpdate = jdbc.update("update vehicle_management set car_state = '" + CarState.getName(1) + "' where license_plate_number = '" + t.getWagonNumber() + "'");
		int driverUpdate = jdbc.update("update driver_management set driver_state = '" +DriverState.getName(1)+ "' where idcrad = '" + t.getIDNumber() +"'");
		String carNumber = t.getWagonNumber();
		double w = t.getNetWeight();
		PurchasePoundListVo po = new PurchasePoundListVo();
		po.setCompanyName("复兴煤业");
		po.setPoundNumber(t.getXsPoundNumber());
		po.setPoundUnit("");
		po.setReceiveUnit(t.getClient());
		po.setPlateNumber(t.getWagonNumber());
		po.setNoticeCode(t.getBillNo());
		po.setSpec(t.getMateriel());
		po.setRoughWeight(t.getRoughWeight());
		po.setTare(t.getTare());
		po.setNetWeight(t.getNetWeight());
		po.setEnterTime(t.getLightCarTime());
		po.setOutTime(t.getReconditioningTime());
		rm.put("msg", NumberCovertChinese.NumberToCharacters(carNumber)+"净"+w+"吨");
		rm.put("screenCarNumber", carNumber);
		rm.put("screenCarWeight", "净"+w+"吨");
		rm.put("salePound", po);
		return rm;

	}

	@Override
	public void delete(String id) {
		dao.delete(id);

	}

	@Override
	public SalesPoundList findById(String id) {
		// TODO Auto-generated method stub
		return dao.findOne(id);
	}
	public CommonResult SalePoundList(String start_time,String end_time,String shippingUnit,String xsPoundNumber,String client,String netWeight,String matter_type,String materiel,String wagonNumber,String billNo,String state,Integer page,Integer pageSize) {
		CommonResult result = new CommonResult();
		String sql1 = "SELECT \r\n" + 
				" s.uuid,s.xs_pound_number,s.client,s.materiel,s.wagon_number,s.rough_weight,s.tare,s.net_weight,s.light_car_time,s.reconditioning_time,s.state,s.xs_order_number,s.waybill_number,s.bill_no,s.traffic_unit,s.shipping_unit,s.weigher,s.handset_confirm,s.create_by,s.create_time"+
				" FROM \r\n" + 
				"	db_business_sales_pound_list s \r\n" + 
				" WHERE   1 = 1 ";
		if(start_time != null && !start_time.isEmpty()){
			sql1 += " and s.create_time >= '" + start_time + "'";
		}
		if(end_time != null && !end_time.isEmpty()) {
			 Long cTime= CommonsUtil.stringToTime(end_time)+86400000L;//时间加1天
			 String end=CommonsUtil.timeToString(cTime);
			sql1 += " and s.create_time < '" + end + "'";
		}
		if(shippingUnit != null && !shippingUnit.isEmpty()){
			sql1 += " and s.shipping_unit = '" + shippingUnit + "'";
		}
		if(client!= null && !client.isEmpty()){
			sql1 += " and s.client =  '" + client + "'";
		}
		if(netWeight != null && !netWeight.isEmpty()){
			sql1 += " and s.net_weight  " + netWeight + "";
		}
		if(materiel != null && !materiel.isEmpty()){
			sql1 += " and s.materiel = '" + materiel + "'";
		}
		if(matter_type != null && !matter_type.isEmpty()){
			sql1 += " and s.matter_type = '" + matter_type + "'";
		}
		if(wagonNumber != null && !wagonNumber.isEmpty()){
			sql1 += " and s.wagon_number like  '%" + wagonNumber + "%'";
		}
		if(xsPoundNumber != null && !xsPoundNumber.isEmpty()){
			sql1 += " and s.xs_pound_number like '%" + xsPoundNumber + "%'";
		}
		if(billNo != null && !billNo.isEmpty()){
			sql1 += " and s.bill_no like '%" + billNo + "%'";
		}
		if(state != null && !state.isEmpty()){
			sql1 += " and s.state = '" + state + "'";
		}
		List<Map<String, Object>> listCount = jdbc.queryForList(sql1);
		System.out.println("（*************sql1列表**************）"+sql1);
		//sql1 += " and p.state <> 5 and p.state <> 6 " ;
		sql1 += " order by s.create_time  desc  limit " + (page * pageSize) +"," + pageSize;
		List<Map<String, Object>> list = jdbc.queryForList(sql1);
		result.setCount(listCount.size());
		result.setObj(list);
		result.setMsg("SUCCESS");
		return result;
}
	public Object[][] SalePoundListExport(String start_time,String end_time,String shippingUnit,String xsPoundNumber,String client,String netWeight,String matter_type,String materiel,String wagonNumber,String billNo,String state) {
		String sql1 = "SELECT \r\n" + 
				" s.xs_pound_number,s.client,s.materiel,s.wagon_number,s.rough_weight,s.tare,s.net_weight,s.light_car_time,s.reconditioning_time,s.state,s.xs_order_number,s.waybill_number,s.bill_no,s.traffic_unit,s.shipping_unit,s.weigher,s.handset_confirm,s.create_by,s.create_time"+
				" FROM \r\n" + 
				"	db_business_sales_pound_list s \r\n" + 
				" WHERE   1 = 1 ";
		if(start_time != null && !start_time.isEmpty()){
			sql1 += " and s.create_time >= '" + start_time + "'";
		}
		if(end_time != null && !end_time.isEmpty()) {
			 Long cTime= CommonsUtil.stringToTime(end_time)+86400000L;//时间加1天
			 String end=CommonsUtil.timeToString(cTime);
			sql1 += " and s.create_time < '" + end + "'";
		}
		if(shippingUnit != null && !shippingUnit.isEmpty()){
			sql1 += " and s.shipping_unit = '" + shippingUnit + "'";
		}
		if(client!= null && !client.isEmpty()){
			sql1 += " and s.client =  '" + client + "'";
		}
		if(netWeight != null && !netWeight.isEmpty()){
			sql1 += " and s.net_weight  " + netWeight + "";
		}
		if(materiel != null && !materiel.isEmpty()){
			sql1 += " and s.materiel = '" + materiel + "'";
		}
		if(matter_type != null && !matter_type.isEmpty()){
			sql1 += " and s.matter_type = '" + matter_type + "'";
		}
		if(wagonNumber != null && !wagonNumber.isEmpty()){
			sql1 += " and s.wagon_number like  '%" + wagonNumber + "%'";
		}
		if(xsPoundNumber != null && !xsPoundNumber.isEmpty()){
			sql1 += " and s.xs_pound_number like '%" + xsPoundNumber + "%'";
		}
		if(billNo != null && !billNo.isEmpty()){
			sql1 += " and s.bill_no like '%" + billNo + "%'";
		}
		if(state != null && !state.isEmpty()){
			sql1 += " and s.state = '" + state + "'";
		}
		System.out.println("（*************sql1列表**************）"+sql1);
		//sql1 += " and p.state <> 5 and p.state <> 6 " ;
		sql1 += " order by s.create_time  desc ";
		System.out.println(sql1+"<!!!!!!!!!!!!!!!!!!!!");
		List<Map<String, Object>> list = jdbc.queryForList(sql1);
		int size = list.size();
	    Object[][] array = new Object[size][19];//数据二维数组
	    String[] arr={"过磅单号","客户","物料","车号","毛重","皮重","净重","轻车时间","重车时间","完成状态","订单号","发运单号","提货单号","运输单位","发货单位","过磅员","手持机确认人","制单人","制单时间"};
	    for (int i =0; i < size; i++) {//循环遍历所有行
	        array[i] = list.get(i).values().toArray();//每行的列数
	    }
	    //表头 一维数组转进二维
	    int n = 19;//列
	    int m = arr.length/ n ;//行
	    Object [][] b = new Object[m][n];
	    int num = -1;
	    for (int i = 0; i < m; i++) {
	    for (int j = 0; j < n; j++) {
	    num++;
	    b[i][j] = arr[num];
	    }
	    }
	    //合并数组
	    Object[][] strs3 = Array.unite(b, array);
	    
		return strs3;
}
	//新销售磅单列表导出
	public void SalePoundListExports(String start_time,String end_time,String shippingUnit,String xsPoundNumber,String client,String netWeight,String matter_type,String materiel,String wagonNumber,String billNo,String state,HttpServletResponse response) {
		String sql1 = "SELECT \r\n" +  
				" s.xs_pound_number,s.client,s.materiel,s.wagon_number,s.rough_weight,s.tare,s.net_weight,s.light_car_time,s.reconditioning_time,s.state,s.xs_order_number,s.waybill_number,s.bill_no,s.traffic_unit,s.shipping_unit,s.weigher,s.handset_confirm,s.create_by,s.create_time"+
				" FROM \r\n" + 
				"	db_business_sales_pound_list s \r\n" + 
				" WHERE   1 = 1 ";
		if(start_time != null && !start_time.isEmpty()){
			sql1 += " and s.create_time >= '" + start_time + "'";
		}
		if(end_time != null && !end_time.isEmpty()) {
			 Long cTime= CommonsUtil.stringToTime(end_time)+86400000L;//时间加1天
			 String end=CommonsUtil.timeToString(cTime);
			sql1 += " and s.create_time < '" + end + "'";
		}
		if(shippingUnit != null && !shippingUnit.isEmpty()){
			sql1 += " and s.shipping_unit = '" + shippingUnit + "'";
		}
		if(client!= null && !client.isEmpty()){
			sql1 += " and s.client =  '" + client + "'";
		}
		if(netWeight != null && !netWeight.isEmpty()){
			sql1 += " and s.net_weight  " + netWeight + "";
		}
		if(materiel != null && !materiel.isEmpty()){
			sql1 += " and s.materiel = '" + materiel + "'";
		}
		if(matter_type != null && !matter_type.isEmpty()){
			sql1 += " and s.matter_type = '" + matter_type + "'";
		}
		if(wagonNumber != null && !wagonNumber.isEmpty()){
			sql1 += " and s.wagon_number like  '%" + wagonNumber + "%'";
		}
		if(xsPoundNumber != null && !xsPoundNumber.isEmpty()){
			sql1 += " and s.xs_pound_number like '%" + xsPoundNumber + "%'";
		}
		if(billNo != null && !billNo.isEmpty()){
			sql1 += " and s.bill_no like '%" + billNo + "%'";
		}
		if(state != null && !state.isEmpty()){
			sql1 += " and s.state = '" + state + "'";
		}
		System.out.println("（*************sql1列表**************）"+sql1);
		//sql1 += " and p.state <> 5 and p.state <> 6 " ;
		sql1 += " order by s.create_time  desc ";
		System.out.println(sql1+"<!!!!!!!!!!!!!!!!!!!!");
		List<Map<String, Object>> list = jdbc.queryForList(sql1);
		int size = list.size();
	    Object[][] array = new Object[size][19];//数据二维数组
	    String[] arr={"过磅单号","客户","物料","车号","毛重","皮重","净重","轻车时间","重车时间","完成状态","订单号","发运单号","提货单号","运输单位","发货单位","过磅员","手持机确认人","制单人","制单时间"};
	  //1.创建excel
	  		HSSFWorkbook wk = new HSSFWorkbook();
	  		CellStyle cs = wk.createCellStyle();
	  		CellStyle cs1 = wk.createCellStyle();
	  		cs.setBorderLeft(CellStyle.BORDER_THIN);
	          cs.setBorderRight(CellStyle.BORDER_THIN);
	          cs.setBorderTop(CellStyle.BORDER_THIN);
	          cs.setBorderBottom(CellStyle.BORDER_THIN);
	          cs.setAlignment(CellStyle.ALIGN_CENTER);
	          cs1.setAlignment(CellStyle.ALIGN_CENTER);
	  		//2.创建一张工作表
	  		HSSFSheet sheet = wk.createSheet();
	  		sheet.setColumnWidth(0, 256*20+150);
	  		sheet.setColumnWidth(1, 256*12+150);
	  		sheet.setColumnWidth(2, 256*12+150);
	  		sheet.setColumnWidth(3, 256*12+150);
	  		sheet.setColumnWidth(4, 256*18+150);
	  		sheet.setColumnWidth(5, 256*12+150);
	  		sheet.setColumnWidth(6, 256*12+150);
	  		sheet.setColumnWidth(7, 256*18+150);
	  		sheet.setColumnWidth(8, 256*18+150);
	  		sheet.setColumnWidth(9, 256*12+150);
	  		sheet.setColumnWidth(10,256*20+150);
	  		sheet.setColumnWidth(11,256*20+150);
	  		sheet.setColumnWidth(12,256*20+150);
	  		sheet.setColumnWidth(13,256*20+150);
	  		sheet.setColumnWidth(14,256*20+150);
	  		sheet.setColumnWidth(15,256*12+150);
	  		sheet.setColumnWidth(16,256*12+150);
	  		sheet.setColumnWidth(17,256*12+150);
	  		sheet.setColumnWidth(18,256*20+150);
	  		
	  		sheet.setDefaultRowHeightInPoints(50*50);
	  		
	  		HSSFPrintSetup printSetup = sheet.getPrintSetup();
	  		printSetup.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); // 纸张A5_PAPERSIZE  A4_PAPERSIZE
	  		sheet.setDisplayGridlines(false);
	  		sheet.setPrintGridlines(false);
	  		sheet.setMargin(HSSFSheet.TopMargin,( double ) 0.2 ); // 上边距
	  		sheet.setMargin(HSSFSheet.BottomMargin,( double ) 0.2 ); // 下边距
	  		sheet.setMargin(HSSFSheet.LeftMargin,( double ) 0.2 ); // 左边距
	  		sheet.setMargin(HSSFSheet.RightMargin,( double ) 0.2 ); // 右边距
	  		//3.设置工作表的列宽
	  		//sheet.setColumnWidth(0, 5000);
	  		//4.设置excel表头（创建第一行）行.列
	  		HSSFRow row0 = sheet.createRow(0);
	  		HSSFRow rowa = sheet.createRow(1);
	  		HSSFRow rowb = sheet.createRow(2);
	  		row0.setHeightInPoints(20);//目的是想把行高设置成20px
	  		HSSFRow row = sheet.createRow(3);
	  		HSSFCell cell0 = row0.createCell(0);
	  		HSSFCell cella = rowa.createCell(0);
	  		HSSFCell cellb = rowb.createCell(0);
	  		HSSFCell cell = row.createCell(0);
	  		
	  		// 设置字体
	  		CellStyle redStyle = wk.createCellStyle();
	  		//居中
	  		redStyle.setAlignment(CellStyle.ALIGN_CENTER);
	  		cs1.setAlignment(CellStyle.ALIGN_LEFT);
	  		
	  		//cell.setCellStyle(redStyle);
	  		cell0.setCellStyle(redStyle);
	  		cella.setCellStyle(cs1);
	  		cellb.setCellStyle(cs1);
	  		HSSFFont font2 = wk.createFont();
	  		//字体
	  		//redFont.setFontName("宋体");
	  		//redStyle.setFontName("黑体");
	  		font2.setColor(Font.COLOR_RED);//红色
	  		font2.setFontName("仿宋_GB2312");
	  		font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
	  		//设置字体大小
	  		font2.setFontHeightInPoints((short) 17);

	  		redStyle.setFont(font2);//选择需要用到的字体格式
	  		//cell0.setCellStyle(cs);
	  		
	  		//字段样式---》
	  		// 设置字体
	  		CellStyle css = wk.createCellStyle();

	  		CellStyle redStyle2 = wk.createCellStyle();
	  		//居中
	  		redStyle2.setAlignment(CellStyle.ALIGN_CENTER);
	  		redStyle2.setBorderLeft(CellStyle.BORDER_THIN);
	  		redStyle2.setBorderRight(CellStyle.BORDER_THIN);
	  		redStyle2.setBorderTop(CellStyle.BORDER_THIN);
	  		redStyle2.setBorderBottom(CellStyle.BORDER_THIN);
	  		HSSFFont font3 = wk.createFont();
	  		//字体
	  		font3.setColor(Font.BOLDWEIGHT_BOLD);//红色
	  		//font3.setFontName("仿宋_GB2312");黑体
	  		//font3.setFontName("黑体");
	  		font3.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
	  		//设置字体大小
	  		font3.setFontHeightInPoints((short) 13);

	  		redStyle2.setFont(font3);//选择需要用到的字体格式
	  		//————————————————————————————
	  		String s_time="2019-03-28 09:27:57";
	  		String e_time="2019-06-28 09:27:57";
	  		String company="复兴煤业有限公司";
	  		cell0.setCellValue("山西复兴煤业有限公司销售磅单表");
	  		cella.setCellValue("日期："+start_time+"至"+end_time);
	  		cellb.setCellValue("条件："+company);
	  	// String[] arr={"过磅单号","客户","物料","车号","毛重","皮重","净重","轻车时间","重车时间","完成状态","订单号","发运单号","提货单号","运输单位","发货单位","过磅员","手持机确认人","制单人","制单时间"};

	  		cell = row.createCell(0);
	  		//cell.setCellStyle(cs);
	  		cell.setCellStyle(redStyle2);
	  		cell.setCellValue("过磅单号");
	  		
	  		cell = row.createCell(1);
	  		cell.setCellStyle(redStyle2);
	  		cell.setCellValue("客户");
	  		
	  		cell = row.createCell(2);
	  		cell.setCellStyle(redStyle2);
	  		cell.setCellValue("物料");
	  		
	  		cell = row.createCell(3);
	  		cell.setCellStyle(redStyle2);
	  		cell.setCellValue("车号");
	  		
	  		cell = row.createCell(4);
	  		cell.setCellStyle(redStyle2);
	  		cell.setCellValue("毛重");
	  		
	  		cell = row.createCell(5);
	  		cell.setCellStyle(redStyle2);
	  		cell.setCellValue("皮重");
	  		
	  		cell = row.createCell(6);
	  		cell.setCellStyle(redStyle2);
	  		cell.setCellValue("净重");
	  		
	  		cell = row.createCell(7);
	  		cell.setCellStyle(redStyle2);
	  		cell.setCellValue("轻车时间");
	  		
	  		cell = row.createCell(8);
	  		cell.setCellStyle(redStyle2);
	  		cell.setCellValue("重车时间");
	  		
	  		cell = row.createCell(9);
	  		cell.setCellStyle(redStyle2);
	  		cell.setCellValue("完成状态");
	  		
	  		cell = row.createCell(10);
	  		cell.setCellStyle(redStyle2);
	  		cell.setCellValue("订单号");
	  		
	  		cell = row.createCell(11);
	  		cell.setCellStyle(redStyle2);
	  		cell.setCellValue("发运单号");
	  		
	  		cell = row.createCell(12);
	  		cell.setCellStyle(redStyle2);
	  		cell.setCellValue("提货单号");
	  		
	  		cell = row.createCell(13);
	  		cell.setCellStyle(redStyle2);
	  		cell.setCellValue("运输单位");
	  		cell = row.createCell(14);
	  		cell.setCellStyle(redStyle2);
	  		cell.setCellValue("发货单位");
	  		
	  		cell = row.createCell(15);
	  		cell.setCellStyle(redStyle2);
	  		cell.setCellValue("过磅员");
	  		
	  		cell = row.createCell(16);
	  		cell.setCellStyle(redStyle2);
	  		cell.setCellValue("手持机确认人");
	  		
	  		cell = row.createCell(17);
	  		cell.setCellStyle(redStyle2);
	  		cell.setCellValue("制单人");
	  		
	  		cell = row.createCell(18);
	  		cell.setCellStyle(redStyle2);
	  		cell.setCellValue("制单时间");
	  		
	  		
	  		//5.设置excel导出数据
	  		 // 创建第一行
	          for (short i=0;i<list.size();i++)
	          {
	              row = sheet.createRow(i+4);
	              Cell cell1 = row.createCell(0);
	              Cell cell2 = row.createCell(1);
	              Cell cell3 = row.createCell(2);
	              Cell cell4 = row.createCell(3);
	              Cell cell5 = row.createCell(4);
	              Cell cell6 = row.createCell(5);
	              Cell cell7 = row.createCell(6);
	              Cell cell8 = row.createCell(7);
	              Cell cell9 = row.createCell(8);
	              Cell cell10 = row.createCell(9);
	              Cell cell11 = row.createCell(10);
	              Cell cell12 = row.createCell(11);
	              Cell cell13 = row.createCell(12);
	              Cell cell14 = row.createCell(13);
	              Cell cell15 = row.createCell(14);
	              Cell cell16 = row.createCell(15);
	              Cell cell17 = row.createCell(16);
	              Cell cell18 = row.createCell(17);
	              Cell cell19 = row.createCell(18);

	                cell1.setCellValue(String.valueOf( list.get(i).get("xs_pound_number")));
	                cell1.setCellStyle(cs);
	                
	                cell2.setCellValue(String.valueOf(list.get(i).get("client")));
	                cell2.setCellStyle(cs);
	                
	                cell3.setCellValue(String.valueOf(list.get(i).get("materiel")));
	                cell3.setCellStyle(cs);
	                
	                cell4.setCellValue(String.valueOf(list.get(i).get("wagon_number")));
	                cell4.setCellStyle(cs);
	                
	                cell5.setCellValue(String.valueOf(list.get(i).get("rough_weight")));
	                cell5.setCellStyle(cs);
	                
	                cell6.setCellValue(String.valueOf(list.get(i).get("tare")));
	                cell6.setCellStyle(cs);
	                
	                cell7.setCellValue(String.valueOf(list.get(i).get("net_weight")));
	                cell7.setCellStyle(cs);
	                
	                cell8.setCellValue(String.valueOf(list.get(i).get("light_car_time")));
	                cell8.setCellStyle(cs);

	                cell9.setCellValue(String.valueOf(list.get(i).get("reconditioning_time")));
	                cell9.setCellStyle(cs);
	                
	                cell10.setCellValue(String.valueOf(list.get(i).get("state")));
	                cell10.setCellStyle(cs);
	                
	                cell11.setCellValue(String.valueOf(list.get(i).get("xs_order_number")));
	                cell11.setCellStyle(cs);
	                
	                cell12.setCellValue(String.valueOf(list.get(i).get("waybill_number")));
	                cell12.setCellStyle(cs);

	                cell13.setCellValue(String.valueOf(list.get(i).get("bill_no")));
	                cell13.setCellStyle(cs);
	                
	                cell14.setCellValue(String.valueOf(list.get(i).get("traffic_unit")));
	                cell14.setCellStyle(cs);
	                
	                cell15.setCellValue(String.valueOf(list.get(i).get("shipping_unit")));
	                cell15.setCellStyle(cs);
	                
	                cell16.setCellValue(String.valueOf(list.get(i).get("weigher")));
	                cell16.setCellStyle(cs);
	                
	                cell17.setCellValue(String.valueOf(list.get(i).get("handset_confirm")));
	                cell17.setCellStyle(cs);
	                
	                cell18.setCellValue(String.valueOf(list.get(i).get("create_by")));
	                cell18.setCellStyle(cs);
	                
	                cell19.setCellValue(String.valueOf(list.get(i).get("create_time")));
	                cell19.setCellStyle(cs);
	                
	            }
	       
	            CellRangeAddress  range= new CellRangeAddress(0,0,0,12);
	            sheet.addMergedRegion(CellRangeAddress.valueOf("$A$2:$R$2"));
	            sheet.addMergedRegion(CellRangeAddress.valueOf("$A$3:$R$3"));  
	            sheet.addMergedRegion(range);
	    		//6.设置excel统计数据
	            try {
	    		//	response.setContentType("application/octet-stream");
	    			//response.setHeader("Content-disposition", "attachment;filename=销售磅单表.xls");//默认Excel名称
	          	  String fileName="销售磅单表";
	        	  response.setContentType("application/vnd.ms-excel");
	        	  response.setCharacterEncoding("utf-8");
	          response.setHeader("content-disposition",
	                     "attachment;filename=" + new String(fileName.getBytes("utf-8"),"ISO-8859-1" )+ ".xls");
	            	response.flushBuffer();
	    			wk.write(response.getOutputStream());
	    			wk.write(new FileOutputStream(new File("D://daochu/销售磅单表明细.xls")));
	    		} catch (FileNotFoundException e) {
	    			e.printStackTrace();
	    		} catch (IOException e) {
	    			e.printStackTrace();
	    		}
}
	public List<Map<String, Object>> findOne(String uuid) {
		String sql = "SELECT \r\n" + 
				" s.*"+
				" FROM \r\n" + 
				"	db_business_sales_pound_list s \r\n" + 
				" WHERE   1 = 1 ";
		if(uuid != null && !uuid.isEmpty()){
			sql += " and s.uuid = '" + uuid + "'";
		}
		List<Map<String, Object>> list = jdbc.queryForList(sql);
		return list;
    }

	@Override
	public void salementaryIncrease(String jsonstr) {
		// TODO Auto-generated method stub
		SalesPoundList t = FastJsonUtils.toBean(jsonstr, SalesPoundList.class);
		t.setCreateTime(SDate.getSystemDateToString());// 设置创建时间
		//t.setLightCarTime(SDate.getSystemDateToString());// 设空车时间
		//t.setReconditioningTime(SDate.getSystemDateToString());// 设置重车时间
		t.setState("2");
		t.setLastModifiedTime(SDate.getSystemDateToString());// 设置最后更新时间
		dao.save(t);
	}

	@Override
	public CommonResult selsaleOrderCode(String startTime, String endTime, String supplier, 
			String material, String orderCode, Integer page, Integer pageSize) {
		// TODO Auto-generated method stub
		CommonResult result = new CommonResult();
		String sql = "select notice_code,order_code,receive_unit,material,create_date from business_delivery_notice_manage where 1=1";
		if (startTime != null && !startTime.equals("")) {
			sql += " and create_date >='" + startTime +"'" ;
		}
		if (endTime != null && !endTime.equals("")) {
			sql += " and create_date <='" + endTime +"'" ;
		}
		if (supplier != null && !supplier.equals("")) {
			sql += " and receive_unit like '%" + supplier + "%'";
		}
		if (material != null && !material.equals("")) {
			sql += " and material like '%" + material +"%'";
		}
		if (orderCode != null && !orderCode.equals("")) {
			sql += " and order_code like '%" +orderCode+ "%'";
		}
		sql +=" order by id desc";
		List<Map<String,Object>> listCount = jdbc.queryForList(sql);
		sql +=" limit " + (page*pageSize) + "," + pageSize;
		System.out.println(sql+"=====");
		List<Map<String,Object>> list = jdbc.queryForList(sql);
		result.setMsg("success");
		result.setObj(list);
		result.setCount(listCount.size());
		return result;
	}

	@Override
	public CommonResult showOhterMes(String orderCode) {
		// TODO Auto-generated method stub
		CommonResult result = new CommonResult();
		String sql = "select order_code,receive_unit,supplier,material,receive_unit,train_number,driver,id_number,accruals,heavy_vehicle_time,empty_car_time from business_delivery_notice_manage where 1=1";
		if (orderCode != null && !orderCode.equals("")) {
			sql += " and notice_code = '" +orderCode+ "'";
		}
		List<Map<String,Object>> list = jdbc.queryForList(sql);
		result.setMsg("success");
		result.setObj(list);
		result.setCount(list.size());
		return result;
	}

	@Override
	public SalesPoundList findByIdCard(String idCard,String state) {
		// TODO Auto-generated method stub
		return dao.findByIDNumberAndState(idCard, state);
	}

	@Override
	public void sendConfirm(SalesPoundList t) {
		dao.save(t);
		
	}
	
	
	
	
	
	
	
}
